package com.yunhe.common.mybatis.intercepts;

import com.yunhe.common.mybatis.domain.QueryCondition;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.List;
import java.util.Locale;
import java.util.Properties;

@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class}),
        @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }) })
public class PageInterceptor implements Interceptor {

    Logger logger=LoggerFactory.getLogger(com.yunhe.common.mybatis.intercepts.PageInterceptor.class);

    // 插件运行的代码，它将代替原有的方法
    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof StatementHandler) {
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
            MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
            SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();

            if (SqlCommandType.SELECT.equals(sqlCommandType)) {
                BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
                Object parameterObject = boundSql.getParameterObject();
                // 只有单参数 继承QueryCondition 或 QueryCondition 本身 才能自动分页
                if (parameterObject instanceof QueryCondition) {
                    QueryCondition queryCondition = (QueryCondition) parameterObject;
                    // 需要分页
                    if (queryCondition.getPageFlag()) {
                        queryCondition.setPageFlag(false);
                        if (queryCondition.getStartRow() != null) {
                            Connection connection = (Connection) invocation.getArgs()[0];

                            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();

                            // 分页参数作为参数对象parameterObject的一个属性
                            String sql = boundSql.getSql();
                            // 重写sql
                            String countSql = concatCountSql(sql,connection);
                            String pageSql = concatPageSql(sql, queryCondition,connection);

                            logger.debug("重写的 count  sql		:" + countSql);
                            logger.debug("重写的 select sql		:" + pageSql);

                            //查询总数
                            PreparedStatement ps = null;

                            ResultSet rs = null;
                            int totalCount = 0;
                            try {
                                ps = connection.prepareStatement(countSql);
                                if (parameterMappings != null) {
                                    for (int i = 0; i < parameterMappings.size(); i++) {
                                        ParameterMapping parameterMapping = parameterMappings.get(i);
                                        if (parameterMapping.getMode() != ParameterMode.OUT) {
                                            Object value;
                                            String propertyName = parameterMapping.getProperty();
                                            //多参数
                                            // issue #448 ask first for additional params
                                            if (boundSql.hasAdditionalParameter(propertyName)) {
                                                value = boundSql.getAdditionalParameter(propertyName);
                                            } else if (parameterObject == null) {
                                                value = null;
                                            } else if (mappedStatement.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass())) {
                                                //单个值
                                                value = parameterObject;
                                            } else {
                                                MetaObject metaObject = mappedStatement.getConfiguration().newMetaObject(parameterObject);
                                                value = metaObject.getValue(propertyName);
                                            }
                                            TypeHandler typeHandler = parameterMapping.getTypeHandler();
                                            JdbcType jdbcType = parameterMapping.getJdbcType();
                                            if (value == null && jdbcType == null) {
                                                jdbcType = mappedStatement.getConfiguration().getJdbcTypeForNull();
                                            }
                                            typeHandler.setParameter(ps, i + 1, value, jdbcType);
                                        }
                                    }
                                }

                                rs = ps.executeQuery();
                                if (rs.next()) {
                                    totalCount = rs.getInt(1);
                                }

                            } catch (SQLException e) {
                                logger.error("Ignore this exception" + e);
                            } finally {
                                try {
                                    if(rs!=null){
                                        rs.close();
                                    }
                                    if(ps!=null){
                                        ps.close();
                                    }
                                } catch (SQLException e) {
                                    logger.error("Ignore this exception" + e);
                                }
                            }

                            metaStatementHandler.setValue("delegate.boundSql.sql",pageSql);
                            // 绑定count
                            queryCondition.setTotalRow(totalCount);
                        }
                    }
                }
            }
        }

        return invocation.proceed();
    }

    /**
     * 拦截类型StatementHandler
     */
    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }

    public String concatCountSql(String sql) {
        StringBuffer sb = new StringBuffer("select count(*) from (");
        sb.append(sql);
        //先去子查询中的子查询
	   /* int a=0;
	    int b=0;
		while(true){
			  a=sql.indexOf("select", a+6);
			  b= sql.indexOf("from",b+4);
			 if(a==-1||a>b){
				 break;
			 }
		}
		sql=sql.substring(b);
		if (sql.lastIndexOf("order") > sql.lastIndexOf(")")) {
			sb.append(sql.substring(sql.indexOf("from") + 4,
					sql.lastIndexOf("order")));
			//子查询全部带limit
		}else{
			sb.append(sql.substring(sql.indexOf("from") + 4));
		}*/
        sb.append(") r");
        return sb.toString();
    }

    public String concatCountSql(String sql,Connection connection){
        //判断是哪种数据库类型
        String driverName="neo4j";
        boolean isNeo4j = false;
        try {
            driverName=connection.getMetaData().getDriverName();
            driverName=driverName.toLowerCase(Locale.ENGLISH);
        } catch (SQLException e) {
            logger.error("驱动名称获取失败");
        }
        if(!StringUtils.isEmpty(driverName) && driverName.startsWith("neo4j")){
            isNeo4j = true;
        }
        StringBuffer sb = new StringBuffer();
        if(isNeo4j){
            //sb.append("start n=node(1) ");
            int aReturn = sql.lastIndexOf("return");
            String newSql = sql.substring(0, aReturn);
            sb.append(newSql);
            sb.append("return count(*)");
        }else {
            return  concatCountSql(sql);
        }
        return sb.toString();
    }

    public String concatPageSql(String sql, QueryCondition queryCondition, Connection connection) {
        //判断是那种数据库类型
        //connection.getSchema()
        String driverName="mysql";
        boolean isMysql = false;
        boolean isNeo4j = false;
        try {
            driverName=connection.getMetaData().getDriverName();
            driverName=driverName.toLowerCase(Locale.ENGLISH);
        } catch (SQLException e) {
            logger.error("驱动名称获取失败");
        }
        if(!StringUtils.isEmpty(driverName) && driverName.startsWith("mysql")){
            isMysql = true;
        }else if(!StringUtils.isEmpty(driverName) && driverName.startsWith("neo4j")){
            isNeo4j = true;
        }
        //boolean isMysql=driverName.startsWith("mysql")?true:false;

        StringBuffer sb = new StringBuffer();
        if(isMysql && !isNeo4j){
            sb.append(sql);
            sb.append(" limit ").append(queryCondition.getStartRow())
                    .append(" , ").append(queryCondition.getSize());
        }else if(!isMysql && isNeo4j){
            sb.append(sql);
            sb.append(" skip ").append(queryCondition.getStartRow())
                    .append(" limit ").append(queryCondition.getSize());
        } else {
            sb.append("select * from (");
            sql= sql.substring(0,7).concat(" rownum rn ,").concat(sql.substring(8));
            logger.info("sql=="+sql);
            sb.append(sql);
            sb.append(") ");
            // 必须要一个and 或者 where
            sb.append("  where ");
//			sb.append("  rn between ").append(queryCondition.getStartRow())
//					.append(" and  ").append((queryCondition.getPageSize()+queryCondition.getStartRow()-1));

            sb.append("  rn > ").append(queryCondition.getStartRow())
                    .append(" and rn <= ").append((queryCondition.getSize()+queryCondition.getStartRow()));
        }
        return sb.toString();
    }

//    //拼接组织sql(单表)
//    public String concatOrgSql(String sql,List<String> orgNos){
//        StringBuffer sb=new StringBuffer();
//        String afterSql=null;
//        if (sql.indexOf("order by")>=0) {
//            String beforeSql= StringUtils.substringBefore(sql, "order by");
//            afterSql=StringUtils.substringAfter(sql, "order by");
//            sb.append(beforeSql);
//            if (beforeSql.indexOf("where")>=0) {
//                sb.append(" and org_no in (");
//            }else{
//                sb.append(" where org_no in (");
//            }
//        }else{
//            sb.append(sql);
//            if (sql.indexOf("where")>=0) {
//                sb.append(" and org_no in (");
//            }else{
//                sb.append(" where org_no in (");
//            }
//        }
//
//        if (!CollectionUtil.isEmpty(orgNos)) {
//            for (int i=0;i<orgNos.size();i++) {
//                sb.append("'"+orgNos.get(i)+"'");
//                if (i<orgNos.size()-1) {
//                    sb.append(",");
//                }
//            }
//            sb.append(")");
//        }
//        if (sql.indexOf("order by")>=0) {
//            sb.append(" order by "+afterSql);
//        }
//        return sb.toString();
//    }
}